#include "DBManager.h"
#include "Logger.h"


using namespace SPlayerServer;
using namespace System;
using namespace System::Text;
using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::Collections::Generic;

SPlayerServer::DBManager::DBManager()
{ }

void SPlayerServer::DBManager::init()
{
}

void SPlayerServer::DBManager::insert(Record ^record)
{
    auto cmdstr = String::Format("INSERT INTO [test].[dbo].[Music]"
                                 " ([Title], [FilePath], [PlayLength], [FileSize], [hasLyric], [LyricPath]) VALUES"
                                 " ('{0}',   '{1}',      {2},          {3},        '{4}',      '{5}')",
                                 encodeText(record->title), record->filepath, record->playLength,
                                 record->fileSize, record->hasLyric, record->lyricPath);


    auto sqlConnection = gcnew SqlConnection(getConnectionString());

    try
    {
        auto command = sqlConnection->CreateCommand();
        command->CommandText = cmdstr;
        sqlConnection->Open();
        int n = command->ExecuteNonQuery();
        delete command;
    }
    catch (Exception ^e)
    {
        Logger::log(LogLevel::Error, "Failed to insert record into Database : {0}", e);
    }
    finally
    {
        sqlConnection->Close();
    }
}

List<Record ^> ^ SPlayerServer::DBManager::query(String ^titleQueryString)
{
    String ^seperator = " ";
    auto keywords = titleQueryString->Split(seperator->ToCharArray());
    StringBuilder b;
    for each (auto k in keywords)
    {
        b.AppendFormat("%{0}% ", encodeText(k));
    }
    auto str = b.ToString(0, b.Length - 1);
    auto cmdstr = String::Format("SELECT TOP 10 [Title], [FilePath], [PlayLength], [FileSize], [hasLyric], [LyricPath]"
                                 " FROM [test].[dbo].[Music] WHERE [Title] like '{0}'",
                                 str);

    auto sqlConnection = gcnew SqlConnection(getConnectionString());

    auto lst = gcnew List < Record ^ > ;
    try
    {
        auto command = sqlConnection->CreateCommand();
        command->CommandText = cmdstr;
        sqlConnection->Open();
        auto reader = command->ExecuteReader();
        while (reader->Read())
        {
            Record ^r = gcnew Record;
            r->title = decodeText(reader->GetString(0));
            r->filepath = reader->GetString(1);
            r->playLength = reader->GetInt32(2);
            r->fileSize = reader->GetInt32(3);
            r->hasLyric = reader->GetBoolean(4);
            r->lyricPath = reader->GetString(5);
            lst->Add(r);
        }
        delete command;
    }
    catch (Exception ^e)
    {
        Logger::log(LogLevel::Error, "Failed to insert record into Database : {0}", e);
    }
    finally
    {
        sqlConnection->Close();
    }
    return lst;
}

Record ^ SPlayerServer::DBManager::getMusic(String ^title)
{
    auto cmdstr = String::Format("SELECT TOP 10 [Title], [FilePath], [PlayLength], [FileSize], [hasLyric], [LyricPath]"
                                 " FROM [test].[dbo].[Music] WHERE [Title] = '{0}'",
                                 encodeText(title));

    auto sqlConnection = gcnew SqlConnection(getConnectionString());

    try
    {
        auto command = sqlConnection->CreateCommand();
        command->CommandText = cmdstr;
        sqlConnection->Open();
        auto reader = command->ExecuteReader();
        delete command;

        if (reader->Read())
        {
            Record ^r = gcnew Record;
            r->title = reader->GetString(0);
            r->filepath = reader->GetString(1);
            r->playLength = reader->GetInt32(2);
            r->fileSize = reader->GetInt32(3);
            r->hasLyric = reader->GetBoolean(4);
            r->lyricPath = reader->GetString(5);
            return r;
        }
    }
    catch (Exception ^e)
    {
        Logger::log(LogLevel::Error, "Failed to insert record into Database : {0}", e);
    }
    finally
    {
        sqlConnection->Close();
    }
    return nullptr;
}
